---
title: "Mysql笔记：数据类型"
date: 2018
categories:
- mysql
tags:
---

<div id="content">
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#orge440786">MySql时间类型：DATETIME &amp; TIMESTAMP</a>
<ul>
<li><a href="#orge9b84fa">长度</a></li>
<li><a href="#orgc41ce42">时区</a></li>
<li><a href="#org0ef6595">JDBC表示时间的类型</a></li>
<li><a href="#org439bf3f">其他命令</a>
<ul>
<li><a href="#org18603da">get current time\_zone</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div class="outline-2" id="outline-container-orge440786">
<h2 id="orge440786">MySql时间类型：DATETIME &amp; TIMESTAMP</h2>
<div class="outline-text-2" id="text-orge440786">
</div>
<div class="outline-3" id="outline-container-orge9b84fa">
<h3 id="orge9b84fa">长度</h3>
<div class="outline-text-3" id="text-orge9b84fa">
<ul class="org-ul">
<li>DATETIME类型包含DATE和TIME，范围1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。DATETIME还能包含毫秒，例如2018-03-11 14::13:22.999</li>
<li>TIMESTAMP也能存储日期和时间，但是范围只有1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UT</li>
</ul>
</div>
</div>
<div class="outline-3" id="outline-container-orgc41ce42">
<h3 id="orgc41ce42">时区</h3>
<div class="outline-text-3" id="text-orgc41ce42">
<ul class="org-ul">
<li>DATETIME存储的时间不包含时区</li>
<li>TIMESTAMP存储的UTC时间</li>
</ul>
<p>
例如：
</p>
<ol class="org-ol">
<li>Create table</li>
</ol>
<pre class="example">
CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);
</pre>
<ol class="org-ol">
<li>insert now（当前时区为+08）</li>
</ol>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">insert</span> <span style="font-weight: bold;">into</span> timestamp_n_datetime(ts,dt) <span style="font-weight: bold;">values</span> (now(), now());
</pre>
</div>
<p>
Select result:
</p>
<p>
MariaDB [test]&gt; select * from timestamp_n_datetime;
</p>
<!-- This HTML table template is generated by emacs 26.3 -->
<table border="1">
<tr>
<td align="left" valign="top">
       id 
    </td>
<td align="left" valign="top">
       ts                  
    </td>
<td align="left" valign="top">
       dt                  
    </td>
</tr>
<tr>
<td align="left" valign="top">
        1 
    </td>
<td align="left" valign="top">
       2018-03-11 14:35:01 
    </td>
<td align="left" valign="top">
       2018-03-11 14:35:01 
    </td>
</tr>
</table>
<p>
1 row in set (0.00 sec)
</p>
<ol class="org-ol">
<li><p>
修改时区
</p>
<p>
MariaDB [test]&gt; set time_zone = '+00:00';
Query OK, 0 rows affected (0.02 sec)
</p></li>
<li><p>
再次查询
</p>
<p>
MariaDB [test]&gt; select * from timestamp_n_datetime;
</p>
<!-- This HTML table template is generated by emacs 26.3 -->
<table border="1">
<tr>
<td align="left" valign="top">
       id 
    </td>
<td align="left" valign="top">
       ts                  
    </td>
<td align="left" valign="top">
       dt                  
    </td>
</tr>
<tr>
<td align="left" valign="top">
        1 
    </td>
<td align="left" valign="top">
       2018-03-11 06:35:01 
    </td>
<td align="left" valign="top">
       2018-03-11 14:35:01 
    </td>
</tr>
</table>
<p>
1 row in set (0.00 sec)
</p></li>
</ol>
<p>
TIMESTAMP存储的时间调整到了新时区，而DATETIME存储的时间不变。刚接触java的时候写代码的就出现过时区问题，mybatis mysql，表字段类型为DATETIME，java类型为java.util.Date。通过查看java connector源码com.mysql.cj.mysqla.MysqlaSession.java的configureTimezone方法，
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold;">this</span>.getServerVariable(<span style="font-style: italic;">"system_time_zone"</span>)
</pre>
</div>
<p>
调试这里发现从系统里面读出来的时区设置是CST。于是去查找数据库服务器的原因
</p>
<p>
MariaDB [(none)]&gt; show variables like '%time_zone%';
</p>
<!-- This HTML table template is generated by emacs 26.3 -->
<table border="1">
<tr>
<td align="left" valign="top">
       Variable_name    
    </td>
<td align="left" valign="top">
       Value  
    </td>
</tr>
<tr>
<td align="left" valign="top">
       system_time_zone <br/>
       time_zone        
    </td>
<td align="left" valign="top">
       CST    <br/>
       SYSTEM 
    </td>
</tr>
</table>
<p>
2 rows in set (0.01 sec)
</p>
<p>
解决方法：
</p>
<p>
修改配置文件 /etc/my.cnf.d/server.cnf (centos 7)，需要重启mysql
</p>
<div class="org-src-container">
<pre class="src src-conf"><span style="font-weight: bold; font-style: italic;"># </span><span style="font-weight: bold; font-style: italic;">this is only for the mysqld standalone daemon</span>
[<span style="font-weight: bold; text-decoration: underline;">mysqld</span>]
<span style="font-weight: bold; font-style: italic;">default-time-zone</span>=<span style="font-style: italic;">'+08:00'</span> <span style="font-weight: bold; font-style: italic;"># </span><span style="font-weight: bold; font-style: italic;">将这行加到mysqld节点下</span>
</pre>
</div>
<p>
修改数据库服务变量（未验证，如果不方便修改配置文件或重启数据库。重启数据库之后失效？？）
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">set</span> time_zone=<span style="font-style: italic;">'+08:00'</span>;
<span style="font-weight: bold;">set</span> <span style="font-weight: bold;">global</span> time_zone=<span style="font-style: italic;">'+08:00'</span>;
flush <span style="font-weight: bold;">privileges</span>;
</pre>
</div>
<p>
如果是直接使用jdbc而不是mybatis，则通过
</p>
<div class="org-src-container">
<pre class="src src-java"><span style="font-weight: bold; text-decoration: underline;">java</span>.<span style="font-weight: bold; text-decoration: underline;">util</span>.<span style="font-weight: bold; text-decoration: underline;">Date</span> <span style="font-weight: bold; font-style: italic;">now</span> = <span style="font-weight: bold;">new</span> <span style="font-weight: bold; text-decoration: underline;">java</span>.<span style="font-weight: bold; text-decoration: underline;">util</span>.<span style="font-weight: bold; text-decoration: underline;">Date</span>();
<span style="font-weight: bold; text-decoration: underline;">java</span>.<span style="font-weight: bold; text-decoration: underline;">sql</span>.<span style="font-weight: bold; text-decoration: underline;">Timestamp</span> <span style="font-weight: bold; font-style: italic;">sqlTimestamp</span> = <span style="font-weight: bold;">new</span> <span style="font-weight: bold; text-decoration: underline;">java</span>.<span style="font-weight: bold; text-decoration: underline;">sql</span>.<span style="font-weight: bold; text-decoration: underline;">Timestamp</span>(now.getTime());
PreparedStatement.setTimestamp(1, sqlTimestamp, Calendar.getInstance(<span style="font-weight: bold; text-decoration: underline;">Locale</span>.CHINA)); 
</pre>
</div>
<p>
即指定第三个参数。如果没有指定第三个参数，则又使用了CST时间（setTimestamp源码中）
</p>
</div>
</div>
<div class="outline-3" id="outline-container-org0ef6595">
<h3 id="org0ef6595">JDBC表示时间的类型</h3>
<div class="outline-text-3" id="text-org0ef6595">
<p>
这一节的资料有点老（<a href="https://www.cis.upenn.edu/~bcpierce/courses/629/jdkdocs/guide/jdbc/getstart/mapping.doc.html">https://www.cis.upenn.edu/~bcpierce/courses/629/jdkdocs/guide/jdbc/getstart/mapping.doc.html</a>）
</p>
<p>
这三种类型都继承自java.util.Date，因为java.util.Date没法匹配三种JDBC时间类型。
</p>
<ul class="org-ul">
<li>DATE表示只包含了年月日的时间类型 java.sql.Date for SQL DATE（这里的SQL说的是SQL-92标准）。 父类的hour, minute, second, millisecond字段被设置为0。</li>
<li>TIME表示只包含了时分秒的时间类型 java.sql.Time for SQL TIME information. 父类的year, month, day字段被设置为1970, 1月, 1号. 这个时间是java里面的“零纪元”</li>
<li>TIMESTAMP包含了DATE+TIME再加上一个纳秒（nanosecond）字段 java.sql.Timestamp for SQL TIMESTAMP information. 这个类型为java.util.Date扩展了额外的nanosecond字段。</li>
</ul>
</div>
</div>
<div class="outline-3" id="outline-container-org439bf3f">
<h3 id="org439bf3f">其他命令</h3>
<div class="outline-text-3" id="text-org439bf3f">
</div>
<div class="outline-4" id="outline-container-org18603da">
<h4 id="org18603da">get current time\_zone</h4>
<div class="outline-text-4" id="text-org18603da">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">SELECT</span> @@<span style="font-weight: bold;">global</span>.<span style="font-weight: bold; text-decoration: underline;">time</span>\_zone, @@<span style="font-weight: bold;">session</span>.<span style="font-weight: bold; text-decoration: underline;">time</span>\_zone;
</pre>
</div>
<p>
如果查询结果是SYSTEM，表示mysql服务器使用了系统时区 {% raw %} date -R {% endraw %} 命令能查看系统时区。
</p>
</div>
</div>
</div>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2018</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
